package cn.portx.view;

import java.io.File;
import java.io.FileOutputStream;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Root;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import com.bstek.bdf3.dorado.jpa.JpaUtil;
import com.bstek.bdf3.dorado.jpa.lin.Linq;
import com.bstek.dorado.annotation.DataProvider;
import com.bstek.dorado.annotation.PropertyDef;
import com.bstek.dorado.data.provider.Criteria;
import com.bstek.dorado.data.provider.Page;
import com.bstek.dorado.uploader.DownloadFile;
import com.bstek.dorado.uploader.annotation.FileProvider;
import com.bstek.dorado.util.DateUtils;

import cn.portx.entity.Customer;
import cn.portx.entity.ExpenseItem;
import cn.portx.entity.HexiaoLog;
import cn.portx.entity.Order;
import cn.portx.entity.OrderExpense;
import cn.portx.entity.ShippingCompany;
import cn.portx.entity.Vessel;
import cn.portx.hibernate.HibernateDao;

@Component
public class ProfitsMxPR {

	@Autowired
	private HibernateDao hibernateDao;

	@Transactional(readOnly = true)
	@DataProvider
	public void loadOrder(Page<Map<String, Object>> page, Criteria criteria, Date hxDateStar, Date hxDateEnd) {
		if (hxDateStar != null && hxDateEnd != null) {
			// Calendar hxDateStarCalendar = Calendar.getInstance();
			// hxDateStarCalendar.set(hxDateStar.getYear(),
			// hxDateStar.getMonth(), hxDateStar.getDay(), 00, 00, 00);
			// Date hxDateStarDate = hxDateStarCalendar.getTime();
			//
			// Calendar hxDateEndCalendar = Calendar.getInstance();
			// hxDateEndCalendar.set(hxDateEnd.getYear(), hxDateEnd.getMonth(),
			// hxDateEnd.getDay(), 23, 59, 59);
			// Date hxDateEndDate = hxDateEndCalendar.getTime();

			List<String> oeId = JpaUtil.linq(HexiaoLog.class, String.class).select("orderExpenseId").greaterThanOrEqualTo("hxDate", hxDateStar)
					.lessThanOrEqualTo("hxDate", hxDateEnd).equal("type", 0).distinct().list();
			if (oeId.size() > 0) {
				List<String> oid = JpaUtil.linq(OrderExpense.class, String.class).select("orderId").in("id", oeId).distinct().list();
				if (oid.size() > 0) {
					JpaUtil.linq(Order.class)
					.collect(Customer.class, "customerId")
					.collect(ShippingCompany.class, "shippingCompanyId")
					.collect(Vessel.class, "vesselId")
					.where(criteria).isNull("targetId").desc("orderDate").in("id", oid).paging(page);
				}
			}
		} else {
			JpaUtil.linq(Order.class)
			.collect(Customer.class, "customerId")
			.collect(ShippingCompany.class, "shippingCompanyId")
			.collect(Vessel.class, "vesselId")
			.where(criteria).isNull("targetId").desc("orderDate").paging(page);
		}
	}

	@Transactional(readOnly = true)
	@DataProvider
	public String loadExpenseItem(Criteria criteria, String expenseItemId) {
		List<ExpenseItem> list = JpaUtil.linq(ExpenseItem.class).where(criteria).equal("id", expenseItemId).list();
		if (list != null) {
			return list.get(0).getName();
		}
		return null;
	}

	@Transactional(readOnly = true)
	@DataProvider
	public List<OrderExpense> loadReceiveExpense(String id, Criteria criteria) {
		return JpaUtil.linq(OrderExpense.class).where(criteria).equal("orderId", id).equal("type", "0").list();
	}

	@Transactional(readOnly = true)
	@DataProvider
	public List<OrderExpense> loadPayExpense(String id, Criteria criteria) {
		return JpaUtil.linq(OrderExpense.class).where(criteria).equal("orderId", id).equal("type", "1").list();
	}

	@Transactional(readOnly = true)
	@DataProvider
	public List<OrderExpense> loadCostExpense(String id, Criteria criteria) {
		return JpaUtil.linq(OrderExpense.class).where(criteria).equal("orderId", id).equal("type", "2").list();
	}

	@Transactional(readOnly = true)
	@DataProvider
	public Customer geCustomer(String customerId) {
		Customer customer = JpaUtil.getOne(Customer.class, customerId);
		if (customer != null) {
			return customer;
		}
		return null;
	}

	@Transactional(readOnly = true)
	@DataProvider
	public Double getProfitsSum(String orderId) {
		Linq linq = JpaUtil.linq(OrderExpense.class,Object[].class);
		Root<OrderExpense> root = linq.root();
		CriteriaBuilder cb = linq.criteriaBuilder();
//		List<Double> values = linq.select(cb.sum(root.get("expenseVal").as(Double.class))).equal("orderId", orderId).groupBy("type").list();
		List<Object[]> values = linq.select(cb.sum(root.get("expenseVal").as(Double.class)),root.get("type").as(String.class)).equal("orderId", orderId).groupBy("type").list();
		if(values.isEmpty()){
			return null;
		}else{
			double shou = 0,fu = 0,chengben = 0;
			for(Object[] array:values){
				double v = (double) array[0];
				String t = (String) array[1];
				if("0".equals(t)){
					shou+=v;
				}else if("1".equals(t)){
					fu +=v;
				}else if("2".equals(t)){
					chengben+=v;
				}
			}
			return shou - fu - chengben;
		}
	}

	@Transactional(readOnly = true)
	@DataProvider
	public Double getSjProfitsSum(String orderId) {
		Linq linq = JpaUtil.linq(OrderExpense.class, Object[].class);
		Root<OrderExpense> root = linq.root();
		CriteriaBuilder cb = linq.criteriaBuilder();
		List<Object[]> values = linq.select(cb.sum(root.get("yxje").as(Double.class)),root.get("type").as(String.class)).equal("orderId", orderId).groupBy("type").list();
		if(values.isEmpty()){
			return null;
		}else{
			double shou = 0,fu=0,chengben=0;
			for(Object[] array:values){
				double v = (double) array[0];
				String t = (String) array[1];
				if("0".equals(t)){
					shou+=v;
				}else if("1".equals(t)){
					fu +=v;
				}
			}
			double sjYsYf = shou - fu;
			List<Double> orderExpenseCb = linq.select(cb.sum(root.get("expenseVal").as(Double.class))).equal("orderId", orderId).equal("type", "2").list();
			if (orderExpenseCb.size() == 1) {
				chengben = orderExpenseCb.get(0);
			}
			return sjYsYf - chengben;
			
		}
		
//		if (values.size() == 3) {
//			Double sjYsYf = values.get(0) - values.get(1);
//			List<Double> orderExpenseCb = linq.select(cb.sum(root.get("expenseVal").as(Double.class))).equal("orderId", orderId).equal("type", "2").list();
//			if (orderExpenseCb.size() == 1) {
//				Double value = sjYsYf - orderExpenseCb.get(0);
//				return value;
//			}
//		}
//		return null;
	}

	@Transactional(readOnly = true)
	@DataProvider
	public ShippingCompany getShippingCompanyName(String companyId, Criteria criteria) {
		List<ShippingCompany> values = JpaUtil.linq(ShippingCompany.class).where(criteria).equal("id", companyId).list();
		if (values.size() > 0) {
			return values.get(0);
		}
		return null;
	}

	@Transactional(readOnly = true)
	@DataProvider
	public Vessel getVesselName(String vesselId, Criteria criteria) {
		List<Vessel> values = JpaUtil.linq(Vessel.class).where(criteria).equal("id", vesselId).list();
		if (values.size() > 0) {
			return values.get(0);
		}
		return null;
	}

	// @Transactional(readOnly = true)
	// @DataProvider
	// public void loadProfits(Page<Order> page, Criteria criteria) {
	// Session session = hibernateDao.getSession(Order.class);
	//
	// String sql =
	// "SELECT T.* FROM (SELECT {O.*},{OS.*},{OSR.*},{ORE.*},{OPE.*},{OCE.*} FROM T_ORDER AS O LEFT JOIN T_ORDER_SEND_RECEIVE AS OS ON O.ID_ = OS.ORDER_ID_ and OS.type_=0 LEFT JOIN t_order_send_receive as OSR on O.id_=OSR.order_id_ and OSR.type_=1 LEFT JOIN T_ORDER_EXPENSE AS ORE ON O.ID_ = ORE.ORDER_ID_ and ORE.type_=0 LEFT JOIN T_ORDER_EXPENSE AS OPE ON O.id_=OPE.order_id_ AND OPE.type_=1 LEFT JOIN T_ORDER_EXPENSE AS OCE ON O.id_=OCE.order_id_ AND OCE.type_=2) AS T WHERE 1=1";
	// String countSql =
	// "SELECT COUNT(O.ID) FROM T_ORDER AS O LEFT JOIN T_ORDER_SEND_RECEIVE AS OSR ON O.ID_ = OSR.ORDER_ID_ LEFT JOIN T_ORDER_EXPENSE AS OE ON O.ID_ = OE.ORDER_ID_";
	// ParseResult pr = hibernateDao.parseCriteria(criteria, true, "T");
	// if (pr != null) {
	// StringBuffer prSql = pr.getAssemblySql();
	// sql += " and " + prSql.toString();
	// }
	// hibernateDao.pagingSQLQuery(page, sql, countSql, null, new
	// ICallback<Void, SQLQuery>() {
	//
	// @Override
	// public Void execute(SQLQuery parameter) {
	// // TODO Auto-generated method stub
	// parameter.addEntity("O", Order.class).addEntity("OS",
	// OrderSendReceive.class)
	// .addEntity("OSR", OrderSendReceive.class).addEntity("ORE",
	// OrderExpense.class)
	// .addEntity("OPE", OrderExpense.class).addEntity("OCE",
	// OrderExpense.class);
	// return null;
	// }
	// }, new ICallback<List<?>, List<?>>() {
	//
	// @Override
	// public List<?> execute(List<?> parameter) {
	// // TODO Auto-generated method stub
	// List<Order> orders = new ArrayList<Order>();
	// for (Object o : parameter) {
	// Object[] object = (Object[]) o;
	// Order order = (Order) object[0];
	// order.setOrderSend((List<OrderSendReceive>) object[1]);
	// order.setOrderReceive((List<OrderSendReceive>) object[2]);
	// order.setReceiveExpense((List<OrderExpense>) object[3]);
	// order.setPayExpense((List<OrderExpense>) object[4]);
	// order.setCostExpense((List<OrderExpense>) object[5]);
	// }
	// return null;
	// }
	// }, session);
	// }

	@FileProvider
	@Transactional(readOnly = true)
	public DownloadFile generatorExcel(Map<String, Object> params) throws Exception {
		//0：应收；1：应付；2：成本
		String ids = (String) params.get("orderIds");
		Object[] orderIds = StringUtils.split(ids, ",");
		String sql = "SELECT DISTINCT\n" +
				"	ei.name_,\n" +
				"	oe.type_,\n" +
				"	oe.expense_item_id_\n" +
				"FROM\n" +
				"	t_order_expense oe\n" +
				"INNER JOIN t_expense_item ei ON oe.expense_item_id_ = ei.id_\n" +
				"WHERE\n" +
				"	oe.order_id_ IN (:orderIds)";
		Session session = this.hibernateDao.getSession(Order.class);
		SQLQuery query = session.createSQLQuery(sql);
		query.setParameterList("orderIds", orderIds);
		List<Object[]> list = query.list();
		Expenses es = new Expenses();
		for (Object[] array : list) {
			String name = (String) array[0];
			String type = (String) array[1];
			String expenseItemId = (String) array[2];
			
			Expense e = new Expense(type,expenseItemId,name);
			es.addExpense(e);
		}
//		Workbook wb = WorkbookFactory.create(file);
		SXSSFWorkbook wb = new SXSSFWorkbook(50);
		Sheet sheet = wb.createSheet("sheet0");
		this.generatorHeader(wb, sheet, es);
		
		sql = "SELECT\n" +
				"	o.id_,\n" +
				"	o.order_no_,\n" +
				"	o.order_date_,\n" +
				"	c.name_ AS customer_name_,\n" +
				"	o.type_,\n" +
				"	o.box_type_,\n" +
				"	o.quantity_,\n" +
				"	o.bill_no_,\n" +
				"	o.cnt_no_,\n" +
				"	o.cnt_no2_,\n" +
				"	o.zxrq_,\n" +
				"	s.name_ AS shipping_company_name_,\n" +
				"	v.name_ AS vessel_name_,\n" +
				"	o.voyage_\n" +
				"FROM\n" +
				"	t_order o\n" +
				"LEFT JOIN t_customer c ON o.customer_id_ = c.id_\n" +
				"LEFT JOIN t_shipping_company s ON o.shipping_company_id_ = s.id_\n" +
				"LEFT JOIN t_vessel v ON o.vessel_id_ = v.id_\n" +
				"WHERE\n" +
				"	o.id_ IN (:orderIds)";
		query = session.createSQLQuery(sql);
		query.setParameterList("orderIds", orderIds);
		list = query.list();
		List<Object[]> datas = new ArrayList<Object[]>();
		for (Object[] array : list) {
			List<Object> d = new ArrayList<Object>();
			
//			Map<String, Object> data = new LinkedHashMap<String, Object>();
//			data.put("order_no_", array[1]);
//			data.put("order_date_", array[2]);
//			data.put("customer_name_", array[3]);
//			data.put("type_", array[4]);
//			data.put("zxdd", getZxdd((String)array[0],session));
//			data.put("box_type_", array[6]);
//			data.put("quantity_", array[7]);
//			data.put("cnt_no_", array[8]);
//			data.put("zxrq_", array[9]);
//			data.put("shipping_company_name_", array[10]);
//			data.put("vessel_name_", array[11]);
//			data.put("voyage_", array[12]);
			
			d.add(array[1]);
			String orderDate = null;
			Date order_date_ = (Date) array[2];
			if(order_date_!=null){
				orderDate = DateUtils.format("yyyy-MM-dd HH:mm:ss", order_date_);
			}
			d.add(orderDate);
			d.add(array[3]);
			String type = (String) array[4];
			d.add("0".equals(type)?"出口":"进口");
			d.add(getZxdd((String)array[0],session));
			d.add(this.getBoxtype((String) array[5]));
			d.add(array[6]);
			d.add(array[7]);
			d.add(array[8]);
			d.add(array[9]);
			String zxrq = null;
			Date zxrq_ = (Date)array[10];
			if(zxrq_!=null){
				zxrq = DateUtils.format("yyyy-MM-dd",zxrq_);
			}
			d.add(zxrq);
			d.add(array[11]);
			d.add(array[12]);
			d.add(array[13]);
			
			ExpenseDatas shou = this.getExpenseData((String)array[0],"0", session);
			Iterator<Expense> iter = es.iterator("0");
			while(iter.hasNext()){
				Expense e = iter.next();
				ExpenseData ed = shou.getExpenseData(e.id);
				d.add(ed==null?null:ed.val);
			}
			d.add(shou.total());
			d.add(shou.totalYxje());
			d.add(shou.totalWxje());
			

			ExpenseDatas fu = this.getExpenseData((String)array[0],"1", session);
			iter = es.iterator("1");
			while(iter.hasNext()){
				Expense e = iter.next();
				ExpenseData ed = fu.getExpenseData(e.id);
				d.add(ed==null?null:ed.val);
			}
			d.add(fu.total());
			d.add(fu.totalYxje());
			d.add(fu.totalWxje());
			
			
			ExpenseDatas cb = this.getExpenseData((String)array[0],"2", session);
			iter = es.iterator("2");
			while(iter.hasNext()){
				Expense e = iter.next();
				ExpenseData ed = cb.getExpenseData(e.id);
				d.add(ed==null?null:ed.val);
			}
			d.add(cb.total());
			
			//利润
			d.add(shou.total()-fu.total()-cb.total());
			d.add(shou.totalYxje()-fu.totalYxje()/*-cb.total()*/);
			
			
			datas.add(d.toArray());
		}
		
		this.insertDataToExcel(sheet, es,datas);

		File file = Files.createTempFile("利润明细", ".xlsx").toFile();
		try(FileOutputStream out = new FileOutputStream(file)){
			wb.write(out);
		}
		wb.close();
		DownloadFile df = new DownloadFile(file);
		return df;
	}

	private String getBoxtype(String boxType) throws NoSuchFieldException, SecurityException{
		PropertyDef pd = Order.class.getDeclaredField("boxType").getAnnotation(PropertyDef.class);
		String s = pd.description();
		String[] arr = StringUtils.split(s, "；");
		for(String item:arr){
			String[] ia = StringUtils.split(item, "：");
			if(StringUtils.equals(boxType, ia[0])){
				return ia[1];
			}
		}
		return null;
	}
	
	private String getZxdd(String id, Session session) {
		String sql = "select osr.address_,osr.factory_name_,osr.type_ from t_order_send_receive osr where osr.order_id_=:orderId";
		SQLQuery query = session.createSQLQuery(sql);
		query.setString("orderId", id);
		List<Object[]> list = query.list();
		Map<String, List<String>> map = new HashMap<String, List<String>>();
		List<String> shou = new ArrayList<String>();
		List<String> fa = new ArrayList<String>();
		for (Object[] array : list) {
			String type = (String) array[2];
			if ("0".equals(type)) {
				String factory = (String) array[1];
				String address = StringUtils.defaultIfBlank((String) array[0], "");
				if (StringUtils.isNotBlank(factory)) {
					if (StringUtils.isNotBlank(address)) {
						shou.add(factory + "(" + address + ")");
					} else {
						shou.add(factory);
					}
				} else {
					if (StringUtils.isNotBlank(address)) {
						shou.add(address);
					}
				}
			} else {
				String factory = (String) array[1];
				String address = StringUtils.defaultIfBlank((String) array[0], "");
				if (StringUtils.isNotBlank(factory)) {
					if (StringUtils.isNotBlank(address)) {
						fa.add(factory + "(" + address + ")");
					} else {
						fa.add(factory);
					}
				} else {
					if (StringUtils.isNotBlank(address)) {
						fa.add(address);
					}
				}
			}
		}
		String s = "";
		if (!fa.isEmpty()) {
			s += "装货地点：" + StringUtils.join(fa, "|");
		}
		if (!shou.isEmpty()) {
			s += "卸货地点：" + StringUtils.join(shou, "|");
		}

		return s;
	}
	
	private ExpenseDatas getExpenseData(String orderId,String type,Session session){
		ExpenseDatas datas = new ExpenseDatas();
		String sql = "SELECT\n" +
				"	oe.type_,\n" +
				"	oe.expense_item_id_,\n" +
				"	oe.expense_val_,\n" +
				"	oe.yxje_,\n" +
				"	oe.wxje_\n" +
				"FROM\n" +
				"	t_order_expense oe\n" +
				"WHERE\n" +
				"	oe.order_id_ =:orderId and oe.type_=:type";
		SQLQuery query = session.createSQLQuery(sql);
		query.setString("orderId", orderId);
		query.setString("type", type);
		List<Object[]> list = query.list();
		if(list==null||list.isEmpty()){
			return datas;
		}else{
			for(Object[] arr:list){
				datas.addExpenseData(new ExpenseData((String)arr[0],(String)arr[1],(Double)arr[2],(Double)arr[3],(Double)arr[4]));
			}
			return datas;
		}
	}

	private void generatorHeader(SXSSFWorkbook wb, Sheet sheet, Expenses es) {
		int shouColumnCount = es.count("0");
		int fuColumnCount = es.count("1");
		int cbColumnCount = es.count("2");

//		HSSFPalette palette = wb.getCustomPalette();
//		palette.setColorAtIndex((short)9, (byte)216, (byte)216, (byte)216);//表头
//		palette.setColorAtIndex((short)10, (byte)216, (byte)216, (byte)216);//标题
//		palette.setColorAtIndex((short)11, (byte)216, (byte)216, (byte)216);//黄色
//		palette.setColorAtIndex((short)12, (byte)216, (byte)216, (byte)216);//灰色
//		palette.setColorAtIndex((short)13, (byte)216, (byte)216, (byte)216);//桃红
		
//		palette.setColorAtIndex((short)10, (byte)245, (byte)230, (byte)230);//标题
//		palette.setColorAtIndex((short)11, (byte)255, (byte)255, (byte)0);//黄色
//		palette.setColorAtIndex((short)12, (byte)226, (byte)239, (byte)218);//灰色
//		palette.setColorAtIndex((short)13, (byte)255, (byte)124, (byte)128);//桃红
		
		Row row1 = sheet.createRow(1);
		row1.setHeightInPoints(30);

		Font font = wb.createFont();
		font.setBold(true);
		
		Cell cell = row1.createCell(0, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		CellStyle cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellValue("订单编号");
		cell.setCellStyle(cs);
		
		
		cell = row1.createCell(1, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("接单日期");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(2, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("客户名称");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(3, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("类型");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(4, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("装卸地点");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 11);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(5, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("箱型");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(6, CellType.NUMERIC);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("数量");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(7, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("提单号");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 11);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(8, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("箱号");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 11);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(9, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("箱号2");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 11);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(10, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("做箱日期");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(11, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("船公司名称");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 12);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(12, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("船名称");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		
		cell = row1.createCell(13, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("航次");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		
		//--------------------------------------------------------------------------------------------
		cell = row1.createCell(14, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("应收费用");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		int col = 15;
		for (int i = 0; i < shouColumnCount + 3 - 1; i++, col++) {
			row1.createCell(col, CellType.BLANK).setCellStyle(cs);
			sheet.setColumnWidth(col, 25*256);
		}
		CellRangeAddress rangeCell = new CellRangeAddress(1, 1, cell.getColumnIndex(), col-1);
		if(!(rangeCell.getFirstRow()==rangeCell.getLastRow() && rangeCell.getFirstColumn()==rangeCell.getLastColumn())){
			sheet.addMergedRegion(rangeCell);
		}
		//--------------------------------------------------------------------------------------------

		//--------------------------------------------------------------------------------------------
		cell = row1.createCell(col, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("应付费用");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		col++;
		for (int i = 0; i < fuColumnCount + 3 - 1; i++, col++) {
			row1.createCell(col, CellType.BLANK).setCellStyle(cs);
			sheet.setColumnWidth(col, 25*256);
		}
		rangeCell = new CellRangeAddress(1, 1, cell.getColumnIndex(), col-1);
		if(!(rangeCell.getFirstRow()==rangeCell.getLastRow() && rangeCell.getFirstColumn()==rangeCell.getLastColumn())){
			sheet.addMergedRegion(rangeCell);
		}
		//--------------------------------------------------------------------------------------------

		//--------------------------------------------------------------------------------------------
		cell = row1.createCell(col, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("成本费用");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		cell.setCellStyle(cs);
		col++;
		for (int i = 0; i < cbColumnCount + 1 - 1; i++, col++) {
			row1.createCell(col, CellType.BLANK).setCellStyle(cs);
			sheet.setColumnWidth(col, 25*256);
		}
		rangeCell = new CellRangeAddress(1, 1, cell.getColumnIndex(), col-1);
		if(!(rangeCell.getFirstRow()==rangeCell.getLastRow() && rangeCell.getFirstColumn()==rangeCell.getLastColumn())){
			sheet.addMergedRegion(rangeCell);
		}
		//--------------------------------------------------------------------------------------------

		cell = row1.createCell(col, CellType.STRING);
		sheet.setColumnWidth(cell.getColumnIndex(), 25*256);
		cell.setCellValue("利润");
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 13);
		cell.setCellStyle(cs);
		row1.createCell(++col, CellType.BLANK).setCellStyle(cs);
		sheet.setColumnWidth(col, 25*256);
		rangeCell = new CellRangeAddress(1, 1, cell.getColumnIndex(), col);
		if(!(rangeCell.getFirstRow()==rangeCell.getLastRow() && rangeCell.getFirstColumn()==rangeCell.getLastColumn())){
			sheet.addMergedRegion(rangeCell);
		}

		//============================================================================================
		Row row2 = sheet.createRow(2);
		row2.setHeightInPoints(40);
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		for (int i = 0; i < 14; i++) {
			row2.createCell(i, CellType.BLANK).setCellStyle(cs);
			rangeCell = new CellRangeAddress(1, 2, i, i);
			if(!(rangeCell.getFirstRow()==rangeCell.getLastRow() && rangeCell.getFirstColumn()==rangeCell.getLastColumn())){
				sheet.addMergedRegion(rangeCell);
			}
		}

		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 9);
		
		int r2Col = 14;
		for(Iterator<Expense> iter = es.iterator("0");iter.hasNext();r2Col++){
			Expense e = iter.next();
//			e.colIndex = r2Col;
			cell = row2.createCell(r2Col, CellType.STRING);
			cell.setCellValue(e.name);
			cell.setCellStyle(cs);
		}
		cell = row2.createCell(r2Col++, CellType.STRING);
		cell.setCellValue("应收合计");
		cell.setCellStyle(cs);
		cell = row2.createCell(r2Col++, CellType.STRING);
		cell.setCellValue("已收");
		cell.setCellStyle(cs);
		cell = row2.createCell(r2Col++, CellType.STRING);
		cell.setCellValue("未收");
		cell.setCellStyle(cs);
		
		for (Iterator<Expense> iter = es.iterator("1"); iter.hasNext(); r2Col++) {
			Expense e = iter.next();
//			e.colIndex = r2Col;
			cell = row2.createCell(r2Col, CellType.STRING);
			cell.setCellValue(e.name);
			cell.setCellStyle(cs);
		}
		cell = row2.createCell(r2Col++, CellType.STRING);
		cell.setCellValue("应付合计");
		cell.setCellStyle(cs);
		cell = row2.createCell(r2Col++, CellType.STRING);
		cell.setCellValue("已付");
		cell.setCellStyle(cs);
		cell = row2.createCell(r2Col++, CellType.STRING);
		cell.setCellValue("未付");
		cell.setCellStyle(cs);
		
		
		for (Iterator<Expense> iter = es.iterator("2"); iter.hasNext(); r2Col++) {
			Expense e = iter.next();
//			e.colIndex = r2Col;
			cell = row2.createCell(r2Col, CellType.STRING);
			cell.setCellValue(e.name);
			cell.setCellStyle(cs);
		}
		cell = row2.createCell(r2Col++, CellType.STRING);
		cell.setCellValue("成本合计");
		cell.setCellStyle(cs);
		
		cell = row2.createCell(r2Col++, CellType.STRING);
		cell.setCellValue("预计");
		cell.setCellStyle(cs);
		cell = row2.createCell(r2Col, CellType.STRING);
		cell.setCellValue("实收");
		cell.setCellStyle(cs);

		//==============================================================================================
		Row row0 = sheet.createRow(0);
		row0.setHeightInPoints(25);
		cs = wb.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setFont(font);
		cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cs.setFillForegroundColor((short) 10);
		for (int i = 0; i < r2Col; i++) {
			if (i == 0) {
				cell = row0.createCell(0, CellType.STRING);
				cell.setCellValue("利润明细");
				cell.setCellStyle(cs);
			} else {
				row0.createCell(i, CellType.BLANK).setCellStyle(cs);
			}
		}
		rangeCell = new CellRangeAddress(0, 0, 0, r2Col);
		if(!(rangeCell.getFirstRow()==rangeCell.getLastRow() && rangeCell.getFirstColumn()==rangeCell.getLastColumn())){
			sheet.addMergedRegion(rangeCell);
		}
	}

	private void insertDataToExcel(Sheet sheet, Expenses es,List<Object[]> datas) {
		CellStyle cs = sheet.getWorkbook().createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setVerticalAlignment(VerticalAlignment.CENTER);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		int rowIndex = 3;
		for (Object[] array : datas) {
			Row row = sheet.createRow(rowIndex++);
			int col = 0;
			for(Object value:array){
				Cell cell =null;
				if(col<14){
					if(value!=null){
						cell = row.createCell(col++,CellType.STRING);
						cell.setCellValue(value.toString());
					}else{
						cell = row.createCell(col++,CellType.BLANK);
					}
					cell.setCellStyle(cs);
				}else{
					if(value!=null){
						cell = row.createCell(col++,CellType.NUMERIC);
						cell.setCellValue(Double.parseDouble(value.toString()));
						CellStyle style = sheet.getWorkbook().createCellStyle();
						style.setAlignment(HorizontalAlignment.CENTER);
						style.setVerticalAlignment(VerticalAlignment.CENTER);
						style.setBorderBottom(BorderStyle.THIN);
						style.setBorderLeft(BorderStyle.THIN);
						style.setBorderRight(BorderStyle.THIN);
						style.setBorderTop(BorderStyle.THIN);
						DataFormat format = sheet.getWorkbook().createDataFormat();
						style.setDataFormat(format.getFormat("0.00"));
						cell.setCellStyle(style);
					}else{
						cell = row.createCell(col++,CellType.BLANK);
						cell.setCellStyle(cs);
					}
				}
			}
		}
	}

	private static class Expenses{
//		private Map<String,Expense> map = new LinkedHashMap<String,Expense>();
		private Map<String,List<Expense>> mapList = new HashMap<String,List<Expense>>();
		
//		public Expense getExpense(String id){
//			return this.map.get(id);
//		}
		
		public Iterator<Expense> iterator(String type){
			List<Expense> list = this.mapList.get(type);
			if(list==null){
				list = new ArrayList<Expense>();
			}
			return list.iterator();
		}
		
		public void addExpense(Expense e){
//			this.map.put(e.id, e);
			List<Expense> list = mapList.get(e.type);
			if(list==null){
				list = new ArrayList<Expense>();
				mapList.put(e.type, list);
			}
			list.add(e);
		}
		
//		public Expense getExpense(String type,int index){
//			return this.mapList.get(type).get(index);
//		}
		
		public int count(String type){
			List<Expense> list = this.mapList.get(type);
			return list==null?0:list.size();
		}
		
	}
	
	private static class Expense{
		private String type;
		private String id;
//		private int colIndex;
		private String name;
		public Expense(String type,String id,String name){
			this.id = id;
			this.type = type;
			this.name = name;
		}
	}
	
	private static class ExpenseDatas{
		private Map<String,ExpenseData> dataMap = new HashMap<String,ExpenseData>();
		
		public void addExpenseData(ExpenseData data){
			this.dataMap.put(data.id, data);
		}
		
		public ExpenseData getExpenseData(String id){
			return this.dataMap.get(id);
		}
		
		public Double total(){
			double d = 0;
			for(ExpenseData ed:dataMap.values()){
				d+=ed.val;
			}
			return d;
		}
		
		public Double totalYxje(){
			double d = 0;
			for(ExpenseData ed:dataMap.values()){
				d+=ed.yxje;
			}
			return d;
		}
		
		public Double totalWxje(){
			double d = 0;
			for(ExpenseData ed:dataMap.values()){
				d+=ed.wxje;
			}
			return d;
		}
		
		
	}
	
	private static class ExpenseData {
		private String type;
		private String id;
		private Double val;
		private Double yxje;
		private Double wxje;
		
		public ExpenseData(String type,String id,Double val,Double yxje,Double wxje){
			this.type = type;
			this.id = id;
			this.val = val;
			this.yxje = yxje;
			this.wxje = wxje;
		}
	}
}
